package com.rybbaby.tss.front.tssMemberCheckAttendance.dao.imp;

import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssMemberCheckAttendance.dao.ITssMemberCheckAttendanceDao;
import com.rybbaby.tss.front.tssMemberCheckAttendance.entity.TssMemberCheckAttendance;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @description 会员考勤记录表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午10:33:46
 */
@Repository
public class TssMemberCheckAttendanceDaoImp extends GenericDao<TssMemberCheckAttendance> implements ITssMemberCheckAttendanceDao {

    /**
     * 根据报班记录ids逻辑删除考勤记录
     * @return
     */
    public int deleteCheckAttendanceRecord(String attendIds){
        String sql = "UPDATE tss_member_check_attendance SET isDelete = 1 WHERE attendClassId IN ('"+attendIds+"')";
        return this.updateBySql(sql);
    }

    @Override
    public int batchAttendance(String paramId, String adminUserId) {
        String sql = "UPDATE tss_member_check_attendance\n" +
                "SET `attendenceStatus` = 1,\n" +
                " adminUserId = '"+adminUserId+"',\n" +
                "updateTime = now()\n" +
                "WHERE\n" +
                "\tid in ("+paramId+")";
        return this.updateBySql(sql);
    }

    @Override
    public List<TssMemberCheckAttendance> attendanceNumber(String lessonsId,int attendenceStatus) throws Exception {
        String sql = "SELECT\n" +
                "\ttss_member_check_attendance.*\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_check_attendance.attendClassId = tss_member_attend_class_record.id\n" +
                "LEFT JOIN tss_class_lessons ON tss_class_lessons.id = tss_member_attend_class_record.lessonsId\n" +
                "WHERE\n" +
                "\ttss_class_lessons.id = '"+lessonsId+"'\n" +
                "AND tss_member_check_attendance.attendenceStatus = "+attendenceStatus+"\n" +
                "AND tss_class_lessons.`status` = 1\n" +
                "AND tss_member_check_attendance.isDelete = 0";
        return this.findListBySql(sql,TssMemberCheckAttendance.class,null);
    }

    @Override
    public List<TssMemberCheckAttendance> sumNumber(String lessonsId) throws Exception{
        String sql = "SELECT\n" +
                "\ttss_member_check_attendance.*\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_check_attendance.attendClassId = tss_member_attend_class_record.id\n" +
                "LEFT JOIN tss_class_lessons ON tss_class_lessons.id = tss_member_attend_class_record.lessonsId\n" +
                "WHERE\n" +
                "\ttss_class_lessons.id = '"+lessonsId+"'\n" +
                "AND tss_class_lessons.`status` = 1\n" +
                "AND tss_member_check_attendance.isDelete = 0";
        return this.findListBySql(sql,TssMemberCheckAttendance.class,null);
    }

    @Override
    public List<TssMemberCheckAttendance> notAttendanceIds(String lessonsId) throws Exception{
        String sql = "SELECT\n" +
                "\ttss_member_check_attendance.*\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_check_attendance.attendClassId = tss_member_attend_class_record.id\n" +
                "LEFT JOIN tss_class_lessons ON tss_class_lessons.id = tss_member_attend_class_record.lessonsId\n" +
                "WHERE\n" +
                "\ttss_class_lessons.id = '"+lessonsId+"'\n" +
                "AND attendenceStatus = 0\n" +
                "AND tss_member_check_attendance.isDelete = 0";
        return this.findListBySql(sql,TssMemberCheckAttendance.class,null);
    }

    @Override
    public int attendance(Integer attendenceStatus, String adminUserId, String id) {
        String sql = "update tss_member_check_attendance set `attendenceStatus`='"+attendenceStatus+"', adminUserId ='"+adminUserId+"',updateTime = now() where id = '"+id+"'";
        return this.updateBySql(sql);
    }

    @Override
    public int Remarks(String remark, String adminUserId, String id) {
        String sql = "update tss_member_check_attendance set `remark`='"+remark+"', adminUserId ='"+adminUserId+"',updateTime = now()where id = '"+id+"'";
        return this.updateBySql(sql);
    }

    @Override
    public List<TssMemberCheckAttendance> findHistory(String paramId) throws Exception {
        String sql ="SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "WHERE\n" +
                "\tid in ("+paramId+")";
         return this.findListBySql(sql,TssMemberCheckAttendance.class,null);
    }

    @Override
    public List<Map<String, Object>> findTodayAttendanceStatisticsByType(String adminUserIds) {
        String sql = "SELECT\n" +
                "\tCOUNT(tss_member_check_attendance.id) AS sumShouldAttendAttendance,\n" +
                "\tCOUNT(CASE\n" +
                "\t\tWHEN attendenceStatus = '1' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND) AS sumRealAttendance,\n" +
                "\ttss_member_check_attendance.type\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\ttss_class_lessons.id,\n" +
                "\t\t\ttss_class_lessons.classId,\n" +
                "\t\t\ttss_member_attend_class_record.memberId\n" +
                "\t\tFROM\n" +
                "\t\t\ttss_member_attend_class_record\n" +
                "\t\tLEFT JOIN tss_class_lessons ON tss_member_attend_class_record.lessonsId = tss_class_lessons.id\n" +
                "\t\tWHERE\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\ttss_class_lessons.classTime\n" +
                "\t\t\t) = to_days(now())\n" +
                "\t\tAND tss_class_lessons.`adminUserId` in ('"+adminUserIds+"')\n" +
                "\t\tAND tss_member_attend_class_record.`status` = '1'\n" +
                "\t\tAND tss_member_attend_class_record.isReturnSection = '0'\n" +
                "\t) A\n" +
                "LEFT JOIN tss_member_check_attendance ON A.classId = tss_member_check_attendance.classId\n" +
                "AND A.memberId = tss_member_check_attendance.memberId\n" +
                "AND to_days(\n" +
                "\ttss_member_check_attendance.createTime\n" +
                ") = to_days(now())\n" +
                "WHERE tss_member_check_attendance.isDelete = '0'\n" +
                "GROUP BY tss_member_check_attendance.type";
        System.out.println(sql);
        return this.findForJdbc(sql,null);
    }

    @Override
    public List<Map<String, Object>>  findClassAttendanceByType(String adminUserIds) {
        /*String sql = "SELECT\n" +
                "\tA.*,\n" +
                " COUNT(\n" +
                "\t\ttss_member_check_attendance.id\n" +
                "\t) AS sumShouldAttendAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '1' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumRealAttendance,\n" +
                "\ttss_member_check_attendance.type,\n" +
                "\ttss_member_check_attendance.createTime\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\ttss_classes.id,\n" +
                "\t\t\ttss_class_lessons.id AS lessonsId,\n" +
                "\t\t\ttss_classes.startTime,\n" +
                "\t\t\ttss_classes.endTime,\n" +
                "\t\t\ttss_classes.`name` AS className,\n" +
                "\t\t\ttss_class_room.`name` AS roomName,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName\n" +
                "\t\tFROM\n" +
                "\t\t\ttss_class_lessons\n" +
                "\t\tLEFT JOIN tss_classes ON tss_class_lessons.classId = tss_classes.id\n" +
                "\t\tLEFT JOIN tss_class_room ON tss_classes.roomId = tss_class_room.id\n" +
                "\t\tLEFT JOIN sys_admin_user ON tss_classes.adminUserId = sys_admin_user.id\n" +
                "\t\tWHERE\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\ttss_class_lessons.classTime\n" +
                "\t\t\t) = to_days(now())\n" +
                "\t\tAND tss_class_lessons.`adminUserId` in ('"+adminUserIds+"')\n" +
                "\t) A\n" +
                "LEFT JOIN tss_member_attend_class_record ON A.lessonsId = tss_member_attend_class_record.lessonsId\n" +
                "LEFT JOIN tss_member_check_attendance ON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.isDelete = '0'\n" +
                "GROUP BY\n" +
                "\tA.id";*/
        /*String sql = "SELECT\n" +
                "\tA.*,\n" +
                " COUNT(\n" +
                "\t\ttss_member_check_attendance.id\n" +
                "\t) AS sumShouldAttendAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '0' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumNotAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '1' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumRealAttendance,\n" +
                "COUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '2' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tELSE 0\n" +
                "\t\tEND\n" +
                "\t) AS sumMakeUpAttendance,\n" +
                "\ttss_member_check_attendance.type,\n" +
                "\ttss_member_check_attendance.createTime\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\ttss_classes.id,\n" +
                "\t\t\ttss_class_lessons.id AS lessonsId,\n" +
                "\t\t\ttss_class_lessons.startTime,\n" +
                "\t\t\ttss_class_lessons.endTime,\n" +
                "\t\t\ttss_classes.`name` AS className,\n" +
                "\t\t\ttss_class_room.`name` AS roomName,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName\n" +
                "\t\tFROM\n" +
                "\t\t\ttss_class_lessons\n" +
                "\t\tLEFT JOIN tss_classes ON tss_class_lessons.classId = tss_classes.id\n" +
                "\t\tLEFT JOIN tss_class_room ON tss_classes.roomId = tss_class_room.id\n" +
                "\t\tLEFT JOIN sys_admin_user ON tss_classes.adminUserId = sys_admin_user.id\n" +
                "\t\tWHERE\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\ttss_class_lessons.classTime\n" +
                "\t\t\t) = to_days(now())\n" +
                "\t\tAND tss_class_lessons.`status` = 1\n" +
                "\t\tAND tss_class_lessons.`adminUserId` in ('"+adminUserIds+"')\n" +
                "\t) A\n" +
                "LEFT JOIN tss_member_attend_class_record ON A.lessonsId = tss_member_attend_class_record.lessonsId\n" +
                "LEFT JOIN tss_member_check_attendance ON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.isDelete = '0'\n" +
                "GROUP BY\n" +
                "\tA.lessonsId\n";*/
        String sql = "SELECT\n" +
                "\tA.*, COUNT(\n" +
                "\t\ttss_member_check_attendance.id\n" +
                "\t) AS sumShouldAttendAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '0' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumNotAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '1' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumRealAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '2' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "\t) AS sumMakeUpAttendance,\n" +
                "\ttss_member_check_attendance.type,\n" +
                "\ttss_member_check_attendance.createTime\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\ttss_classes.id,\n" +
                "\t\t\ttss_class_lessons.id AS lessonsId,\n" +
                "\t\t\ttss_class_lessons.startTime,\n" +
                "\t\t\ttss_class_lessons.endTime,\n" +
                "\t\t\ttss_classes.`name` AS className,\n" +
                "\t\t\ttss_class_room.`name` AS roomName,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName,\n" +
                "\t\t\tsys_course_system.color AS color\n" +
                "\t\tFROM\n" +
                "\t\t\ttss_class_lessons\n" +
                "\t\tLEFT JOIN tss_classes ON tss_class_lessons.classId = tss_classes.id\n" +
                "\t\tLEFT JOIN tss_course ON tss_classes.courseId = tss_course.id\n" +
                "\t\tLEFT JOIN sys_course_system ON tss_course.systemId = sys_course_system.id\n" +
                "\t\tLEFT JOIN tss_class_room ON tss_classes.roomId = tss_class_room.id\n" +
                "\t\tLEFT JOIN sys_admin_user ON tss_classes.adminUserId = sys_admin_user.id\n" +
                "\t\tWHERE\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\ttss_class_lessons.classTime\n" +
                "\t\t\t) = to_days(now())\n" +
                "\t\tAND tss_class_lessons.`status` = 1\n" +
                "\t\tAND tss_class_lessons.`adminUserId` IN ('"+adminUserIds+"')\n" +
                "\t) A\n" +
                "LEFT JOIN tss_member_attend_class_record ON A.lessonsId = tss_member_attend_class_record.lessonsId\n" +
                "LEFT JOIN tss_member_check_attendance ON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.isDelete = '0'\n" +
                "GROUP BY\n" +
                "\tA.lessonsId";
        System.out.println(sql);
        return this.findForJdbc(sql,null);
    }

    @Override
    public Map<String, Object> findPackageInformation(String id) {
        String sql = "SELECT\n" +
                "\ttss_member_check_attendance.id,\n" +
                "\ttss_member_check_attendance.classId,\n" +
                "\ttss_member_check_attendance.memberId,\n" +
                "\ttss_member_check_attendance.formalClass,\n" +
                "\ttss_member_check_attendance.giftClass,\n" +
                "\ttss_class_lessons.id AS lessonsId,\n" +
                "\ttss_member_attend_class_record.memberPackageId,\n" +
                "\ttss_classes.expendClassHour,\n" +
                "\ttss_member_package.nowUnitPrice,\n" +
                "\ttss_member_package.isComplimentaryMeals\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_class_lessons ON tss_member_check_attendance.classId = tss_class_lessons.classId\n" +
                "AND tss_member_check_attendance.createTime = tss_class_lessons.classTime\n" +
                "LEFT JOIN tss_classes ON tss_classes.id = tss_class_lessons.classId\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_attend_class_record.lessonsId = tss_class_lessons.id\n" +
                "AND tss_member_check_attendance.memberId = tss_member_attend_class_record.memberId\n" +
                "LEFT JOIN tss_member_package ON tss_member_attend_class_record.memberPackageId = tss_member_package.id\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.id = '"+id+"' GROUP BY tss_member_check_attendance.id";
        return this.findOneForJdbc(sql, null);
    }

    @Override
    public List<Map<String, Object>> findPackageInformationList(String ids) {
        String sql = "SELECT\n" +
                "\ttss_member_check_attendance.id,\n" +
                "\ttss_member_check_attendance.classId,\n" +
                "\ttss_member_check_attendance.memberId,\n" +
                "\ttss_member_check_attendance.formalClass,\n" +
                "\ttss_member_check_attendance.giftClass,\n" +
                "\ttss_class_lessons.id AS lessonsId,\n" +
                "\ttss_member_attend_class_record.memberPackageId,\n" +
                "\ttss_classes.expendClassHour,\n" +
                "\ttss_member_package.nowUnitPrice,\n" +
                "\ttss_member_package.isComplimentaryMeals\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_class_lessons ON tss_member_check_attendance.classId = tss_class_lessons.classId\n" +
                "AND tss_member_check_attendance.createTime = tss_class_lessons.classTime\n" +
                "LEFT JOIN tss_classes ON tss_classes.id = tss_class_lessons.classId\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_attend_class_record.lessonsId = tss_class_lessons.id\n" +
                "AND tss_member_check_attendance.memberId = tss_member_attend_class_record.memberId\n" +
                "LEFT JOIN tss_member_package ON tss_member_attend_class_record.memberPackageId = tss_member_package.id\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.id in ("+ids+")  GROUP BY tss_member_check_attendance.id";

        return this.findForJdbc(sql, null);
    }

    @Override
    public Map<String, Object> findAttendanceMaintenance(String attendanceId) {
        String sql = "SELECT\n" +
                "\ttss_student.`name` AS studentName,\n" +
                "  tss_classes.`name` AS className,\n" +
                "\ttss_member_check_attendance.attendenceStatus,\n" +
                "\ttss_class_lessons.id,\n" +
                "\ttss_class_lessons.classTime,\n" +
                "\tsys_course_system.`name` AS courseName\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_student ON tss_member_check_attendance.memberId = tss_student.id\n" +
                "LEFT JOIN tss_class_lessons ON tss_class_lessons.classId = tss_member_check_attendance.classId\n" +
                "AND tss_class_lessons.classTime = tss_member_check_attendance.createTime\n" +
                "LEFT JOIN tss_classes ON tss_classes.id = tss_member_check_attendance.classId\n" +
                "LEFT JOIN tss_course ON tss_classes.courseId = tss_course.id\n" +
                "LEFT JOIN sys_course_system ON tss_course.systemId = sys_course_system.id\n" +
                "where tss_member_check_attendance.id = ? ";
        return this.findOneForJdbc(sql, new Object[]{attendanceId});
    }

    @Override
    public int updateCheckAttendance(String adminUserId, String remark, Integer attendenceStatus,String attendanceId) {
        String sql = "UPDATE tss_member_check_attendance\n" +
                "SET `attendenceStatus` = "+attendenceStatus+",\n" +
                " adminUserId = '"+adminUserId+"',\n" +
                " remark = '"+remark+"',\n" +
                "updateTime = now()\n" +
                "WHERE\n" +
                "\tid = '"+attendanceId+"'";
        return this.updateBySql(sql);
    }

    @Override
    public Map<String, Object> findAttendanceMaintenancePackage(String attendanceId) {
        String sql = "";
        return this.findOneForJdbc(sql, new Object[]{attendanceId});
    }

    @Override
    public Map<String, Object> attendanceStatistics(String lessonsId) {
        String sql = "SELECT\n" +
                "\tCOUNT(\n" +
                "\t\ttss_member_check_attendance.id\n" +
                "\t) AS sumShouldAttendAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '0' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumNotAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '1' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumRealAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '2' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "\t) AS sumMakeUpAttendance\n" +
                "FROM\n" +
                "\ttss_class_lessons\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_class_lessons.id = tss_member_attend_class_record.lessonsId\n" +
                "LEFT JOIN tss_member_check_attendance ON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "WHERE\n" +
                "\ttss_class_lessons.id = ?\n" +
                "AND tss_class_lessons.`status` = 1\n" +
                "AND tss_member_check_attendance.isDelete = '0'\n" +
                "GROUP BY\n" +
                "\ttss_class_lessons.id";
        return this.findOneForJdbc(sql, new Object[]{lessonsId});
    }


}
